{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import os\n",
    "from dotenv import load_dotenv\n",
    "# Load environment variables from openai.env file\n",
    "load_dotenv(\"openai.env\")\n",
    "\n",
    "# Read the OPENAI_API_KEY from the environment\n",
    "api_key = os.getenv(\"OPENAI_API_KEY\")\n",
    "api_base = os.getenv(\"OPENAI_API_BASE\")\n",
    "os.environ[\"OPENAI_API_KEY\"] = api_key\n",
    "os.environ[\"OPENAI_API_BASE\"] = api_base"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Tookit\n",
    "tookit是langchain已经封装好的一系列工具，一个工具包是一组工具来组合完成特定的任务\n",
    "<hr>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Azure认知服务 https://portal.azure.com/#allservices\n",
    "- AzureCogsFormRecognizerTool：从文档里提取文本\n",
    "- AzureCogsSpeech2TextTool：语音到文本\n",
    "- AzureCogsText2SpeechTool：文本到语音"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m24.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "! pip install --upgrade azure-ai-formrecognizer > /dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m24.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "! pip install --upgrade azure-cognitiveservices-speech > /dev/null"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Requirement already satisfied: azure-ai-textanalytics in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (5.3.0)\n",
      "Requirement already satisfied: azure-core<2.0.0,>=1.24.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-ai-textanalytics) (1.29.6)\n",
      "Requirement already satisfied: azure-common~=1.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-ai-textanalytics) (1.1.28)\n",
      "Requirement already satisfied: isodate<1.0.0,>=0.6.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-ai-textanalytics) (0.6.1)\n",
      "Requirement already satisfied: typing-extensions>=4.0.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-ai-textanalytics) (4.8.0)\n",
      "Requirement already satisfied: anyio<5.0,>=3.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (3.7.1)\n",
      "Requirement already satisfied: requests>=2.21.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (2.31.0)\n",
      "Requirement already satisfied: six>=1.11.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (1.16.0)\n",
      "Requirement already satisfied: idna>=2.8 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from anyio<5.0,>=3.0->azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (3.4)\n",
      "Requirement already satisfied: sniffio>=1.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from anyio<5.0,>=3.0->azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (1.3.0)\n",
      "Requirement already satisfied: charset-normalizer<4,>=2 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from requests>=2.21.0->azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (3.3.2)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from requests>=2.21.0->azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (2.1.0)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from requests>=2.21.0->azure-core<2.0.0,>=1.24.0->azure-ai-textanalytics) (2023.7.22)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m24.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "! pip install azure-ai-textanalytics"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "\n",
    "os.environ[\"AZURE_COGS_KEY\"] = \"c10\"\n",
    "os.environ[\"AZURE_COGS_ENDPOINT\"] = \"https://eastus.api.cognitive.microsoft.com/\"\n",
    "os.environ[\"AZURE_COGS_REGION\"] = \"eastus\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#创建toolkit\n",
    "from langchain.agents.agent_toolkits import AzureCognitiveServicesToolkit\n",
    "\n",
    "toolkit = AzureCognitiveServicesToolkit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['azure_cognitive_services_form_recognizer',\n",
       " 'azure_cognitive_services_speech2text',\n",
       " 'azure_cognitive_services_text2speech',\n",
       " 'azure_cognitive_services_text_analyics_health']"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[tool.name for tool in toolkit.get_tools()]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The class `langchain_community.chat_models.openai.ChatOpenAI` was deprecated in langchain-community 0.0.10 and will be removed in 0.2.0. An updated version of the class exists in the langchain-openai package and should be used instead. To use it run `pip install -U langchain-openai` and import as `from langchain_openai import ChatOpenAI`.\n",
      "  warn_deprecated(\n",
      "/Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The function `initialize_agent` was deprecated in LangChain 0.1.0 and will be removed in 0.2.0. Use Use new agent constructor methods like create_react_agent, create_json_agent, create_structured_chat_agent, etc. instead.\n",
      "  warn_deprecated(\n"
     ]
    }
   ],
   "source": [
    "#agent使用\n",
    "from langchain.chat_models import ChatOpenAI\n",
    "from langchain.agents import initialize_agent, AgentType\n",
    "llm = ChatOpenAI(temperature=0,model=\"gpt-4-1106-preview\")\n",
    "agent = initialize_agent(\n",
    "    tools=toolkit.get_tools(),\n",
    "    llm=llm,\n",
    "    agent=AgentType.STRUCTURED_CHAT_ZERO_SHOT_REACT_DESCRIPTION,\n",
    "    verbose=True,\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The function `run` was deprecated in LangChain 0.1.0 and will be removed in 0.2.0. Use invoke instead.\n",
      "  warn_deprecated(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mThought: I will provide a joke and then use the Text2Speech tool to read it out loud for the user.\n",
      "\n",
      "Action:\n",
      "```json\n",
      "{\n",
      "  \"action\": \"azure_cognitive_services_text2speech\",\n",
      "  \"action_input\": {\n",
      "    \"query\": \"Why don't scientists trust atoms? Because they make up everything!\"\n",
      "  }\n",
      "}\n",
      "```\n",
      "\u001b[0m\n",
      "Observation: \u001b[38;5;200m\u001b[1;3m/var/folders/19/z2pt1pw16b5bgy5jbbv9xkmh0000gn/T/tmpftnr2hfg.wav\u001b[0m\n",
      "Thought:\u001b[32;1m\u001b[1;3mAction:\n",
      "```json\n",
      "{\n",
      "  \"action\": \"Final Answer\",\n",
      "  \"action_input\": \"Please find the audio file with the joke read out loud attached.\"\n",
      "}\n",
      "```\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "audio_file = agent.run(\"Tell me a joke and read it out for me.\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Please find the audio file with the joke read out loud attached.\n"
     ]
    }
   ],
   "source": [
    "print(audio_file)\n",
    "#from IPython import display\n",
    "\n",
    "#audio = display.Audio(audio_file)\n",
    "#display.display(audio)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## python\n",
    "一个python代码机器人\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Looking in indexes: https://pypi.tuna.tsinghua.edu.cn/simple\n",
      "Requirement already satisfied: langchain_experimental in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (0.0.49)\n",
      "Requirement already satisfied: langchain<0.2,>=0.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain_experimental) (0.1.12)\n",
      "Requirement already satisfied: langchain-core<0.2.0,>=0.1.7 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain_experimental) (0.1.31)\n",
      "Requirement already satisfied: PyYAML>=5.3 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (6.0.1)\n",
      "Requirement already satisfied: SQLAlchemy<3,>=1.4 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (2.0.23)\n",
      "Requirement already satisfied: aiohttp<4.0.0,>=3.8.3 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (3.8.6)\n",
      "Requirement already satisfied: dataclasses-json<0.7,>=0.5.7 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (0.5.14)\n",
      "Requirement already satisfied: jsonpatch<2.0,>=1.33 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (1.33)\n",
      "Requirement already satisfied: langchain-community<0.1,>=0.0.28 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (0.0.28)\n",
      "Requirement already satisfied: langchain-text-splitters<0.1,>=0.0.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (0.0.1)\n",
      "Requirement already satisfied: langsmith<0.2.0,>=0.1.17 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (0.1.25)\n",
      "Requirement already satisfied: numpy<2,>=1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (1.26.1)\n",
      "Requirement already satisfied: pydantic<3,>=1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (1.10.14)\n",
      "Requirement already satisfied: requests<3,>=2 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (2.31.0)\n",
      "Requirement already satisfied: tenacity<9.0.0,>=8.1.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain<0.2,>=0.1->langchain_experimental) (8.2.3)\n",
      "Requirement already satisfied: anyio<5,>=3 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain-core<0.2.0,>=0.1.7->langchain_experimental) (3.7.1)\n",
      "Requirement already satisfied: packaging<24.0,>=23.2 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langchain-core<0.2.0,>=0.1.7->langchain_experimental) (23.2)\n",
      "Requirement already satisfied: attrs>=17.3.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (23.1.0)\n",
      "Requirement already satisfied: charset-normalizer<4.0,>=2.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (3.3.2)\n",
      "Requirement already satisfied: multidict<7.0,>=4.5 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (6.0.4)\n",
      "Requirement already satisfied: async-timeout<5.0,>=4.0.0a3 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (4.0.3)\n",
      "Requirement already satisfied: yarl<2.0,>=1.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (1.9.2)\n",
      "Requirement already satisfied: frozenlist>=1.1.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (1.4.0)\n",
      "Requirement already satisfied: aiosignal>=1.1.2 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from aiohttp<4.0.0,>=3.8.3->langchain<0.2,>=0.1->langchain_experimental) (1.3.1)\n",
      "Requirement already satisfied: idna>=2.8 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from anyio<5,>=3->langchain-core<0.2.0,>=0.1.7->langchain_experimental) (3.4)\n",
      "Requirement already satisfied: sniffio>=1.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from anyio<5,>=3->langchain-core<0.2.0,>=0.1.7->langchain_experimental) (1.3.0)\n",
      "Requirement already satisfied: marshmallow<4.0.0,>=3.18.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from dataclasses-json<0.7,>=0.5.7->langchain<0.2,>=0.1->langchain_experimental) (3.20.1)\n",
      "Requirement already satisfied: typing-inspect<1,>=0.4.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from dataclasses-json<0.7,>=0.5.7->langchain<0.2,>=0.1->langchain_experimental) (0.9.0)\n",
      "Requirement already satisfied: jsonpointer>=1.9 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from jsonpatch<2.0,>=1.33->langchain<0.2,>=0.1->langchain_experimental) (2.4)\n",
      "Requirement already satisfied: orjson<4.0.0,>=3.9.14 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from langsmith<0.2.0,>=0.1.17->langchain<0.2,>=0.1->langchain_experimental) (3.9.15)\n",
      "Requirement already satisfied: typing-extensions>=4.2.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from pydantic<3,>=1->langchain<0.2,>=0.1->langchain_experimental) (4.8.0)\n",
      "Requirement already satisfied: urllib3<3,>=1.21.1 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from requests<3,>=2->langchain<0.2,>=0.1->langchain_experimental) (2.1.0)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from requests<3,>=2->langchain<0.2,>=0.1->langchain_experimental) (2023.7.22)\n",
      "Requirement already satisfied: mypy-extensions>=0.3.0 in /Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages (from typing-inspect<1,>=0.4.0->dataclasses-json<0.7,>=0.5.7->langchain<0.2,>=0.1->langchain_experimental) (1.0.0)\n",
      "\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m A new release of pip is available: \u001b[0m\u001b[31;49m23.2.1\u001b[0m\u001b[39;49m -> \u001b[0m\u001b[32;49m24.0\u001b[0m\n",
      "\u001b[1m[\u001b[0m\u001b[34;49mnotice\u001b[0m\u001b[1;39;49m]\u001b[0m\u001b[39;49m To update, run: \u001b[0m\u001b[32;49mpip install --upgrade pip\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "! pip install langchain_experimental"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain_experimental.agents.agent_toolkits import create_python_agent\n",
    "from langchain_experimental.tools import PythonREPLTool\n",
    "from langchain_experimental.utilities import PythonREPL\n",
    "from langchain.llms.openai import OpenAI\n",
    "from langchain.agents.agent_types import AgentType\n",
    "from langchain.chat_models import ChatOpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = create_python_agent(\n",
    "    llm=ChatOpenAI(temperature=0, model=\"gpt-4-1106-preview\"),\n",
    "    tool=PythonREPLTool(),\n",
    "    verbose=True,\n",
    "    agent_type=AgentType.OPENAI_FUNCTIONS,\n",
    "    agent_executor_kwargs={\"handle_parsing_errors\": True},\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mTo find the 10th Fibonacci number, I will write and execute a Python code snippet that calculates Fibonacci numbers.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'To find the 10th Fibonacci number, I will write and execute a Python code snippet that calculates Fibonacci numbers.'"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\"What is the 10th fibonacci number?\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3mTo accomplish this task, I will follow these steps in PyTorch:\n",
      "\n",
      "1. Import necessary libraries.\n",
      "2. Create synthetic data for the function \\(y = 2x\\).\n",
      "3. Define a single neuron neural network.\n",
      "4. Define a loss function and an optimizer.\n",
      "5. Train the network for 1000 epochs, printing the loss every 100 epochs.\n",
      "6. Predict the output for \\(x = 5\\) after training.\n",
      "\n",
      "Let's start by writing and executing the code.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "\"To accomplish this task, I will follow these steps in PyTorch:\\n\\n1. Import necessary libraries.\\n2. Create synthetic data for the function \\\\(y = 2x\\\\).\\n3. Define a single neuron neural network.\\n4. Define a loss function and an optimizer.\\n5. Train the network for 1000 epochs, printing the loss every 100 epochs.\\n6. Predict the output for \\\\(x = 5\\\\) after training.\\n\\nLet's start by writing and executing the code.\""
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\n",
    "    \"\"\"Understand, write a single neuron neural network in PyTorch.\n",
    "Take synthetic data for y=2x. Train for 1000 epochs and print every 100 epochs.\n",
    "Return prediction for x = 5\"\"\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### SQL Database\n",
    "使用SQLDatabaseChain构建的agent，用来根据数据库回答一般行动饿问题"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "from langchain.agents import create_sql_agent\n",
    "from langchain.agents.agent_toolkits import SQLDatabaseToolkit\n",
    "from langchain.sql_database import SQLDatabase\n",
    "from langchain.llms.openai import OpenAI\n",
    "from langchain.agents import AgentExecutor\n",
    "from langchain.agents.agent_types import AgentType\n",
    "from langchain.chat_models import ChatOpenAI"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The class `langchain_community.llms.openai.OpenAI` was deprecated in langchain-community 0.0.10 and will be removed in 0.2.0. An updated version of the class exists in the langchain-openai package and should be used instead. To use it run `pip install -U langchain-openai` and import as `from langchain_openai import OpenAI`.\n",
      "  warn_deprecated(\n"
     ]
    }
   ],
   "source": [
    "db = SQLDatabase.from_uri(\"sqlite:///Chinook.db\")\n",
    "toolkit = SQLDatabaseToolkit(db=db, llm=OpenAI(temperature=0))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "agent_executor = create_sql_agent(\n",
    "    llm=ChatOpenAI(temperature=0, model=\"gpt-4-1106-preview\"),\n",
    "    toolkit=toolkit,\n",
    "    verbose=True,\n",
    "    agent_type=AgentType.OPENAI_FUNCTIONS\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQL Agent Executor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m```sql\n",
      "PRAGMA table_info(playlisttrack);\n",
      "```\n",
      "\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'```sql\\nPRAGMA table_info(playlisttrack);\\n```\\n'"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\"Describe the playlisttrack table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "用自然语言描述一个数据库表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new SQL Agent Executor chain...\u001b[0m\n"
     ]
    }
   ],
   "source": [
    "agent_executor.run(\"Describe the Playlist table\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "用自然语言跑sql查询"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/tomiezhang/.pyenv/versions/3.10.12/envs/langchains/lib/python3.11/site-packages/langchain_core/_api/deprecation.py:117: LangChainDeprecationWarning: The function `run` was deprecated in LangChain 0.1.0 and will be removed in 0.2.0. Use invoke instead.\n",
      "  warn_deprecated(\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_list_tables` with ``\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[38;5;200m\u001b[1;3mAlbum, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_schema` with `Customer, Invoice`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE \"Customer\" (\n",
      "\t\"CustomerId\" INTEGER NOT NULL, \n",
      "\t\"FirstName\" NVARCHAR(40) NOT NULL, \n",
      "\t\"LastName\" NVARCHAR(20) NOT NULL, \n",
      "\t\"Company\" NVARCHAR(80), \n",
      "\t\"Address\" NVARCHAR(70), \n",
      "\t\"City\" NVARCHAR(40), \n",
      "\t\"State\" NVARCHAR(40), \n",
      "\t\"Country\" NVARCHAR(40), \n",
      "\t\"PostalCode\" NVARCHAR(10), \n",
      "\t\"Phone\" NVARCHAR(24), \n",
      "\t\"Fax\" NVARCHAR(24), \n",
      "\t\"Email\" NVARCHAR(60) NOT NULL, \n",
      "\t\"SupportRepId\" INTEGER, \n",
      "\tPRIMARY KEY (\"CustomerId\"), \n",
      "\tFOREIGN KEY(\"SupportRepId\") REFERENCES \"Employee\" (\"EmployeeId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Customer table:\n",
      "CustomerId\tFirstName\tLastName\tCompany\tAddress\tCity\tState\tCountry\tPostalCode\tPhone\tFax\tEmail\tSupportRepId\n",
      "1\tLuís\tGonçalves\tEmbraer - Empresa Brasileira de Aeronáutica S.A.\tAv. Brigadeiro Faria Lima, 2170\tSão José dos Campos\tSP\tBrazil\t12227-000\t+55 (12) 3923-5555\t+55 (12) 3923-5566\tluisg@embraer.com.br\t3\n",
      "2\tLeonie\tKöhler\tNone\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t+49 0711 2842222\tNone\tleonekohler@surfeu.de\t5\n",
      "3\tFrançois\tTremblay\tNone\t1498 rue Bélanger\tMontréal\tQC\tCanada\tH2G 1A7\t+1 (514) 721-4711\tNone\tftremblay@gmail.com\t3\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE \"Invoice\" (\n",
      "\t\"InvoiceId\" INTEGER NOT NULL, \n",
      "\t\"CustomerId\" INTEGER NOT NULL, \n",
      "\t\"InvoiceDate\" DATETIME NOT NULL, \n",
      "\t\"BillingAddress\" NVARCHAR(70), \n",
      "\t\"BillingCity\" NVARCHAR(40), \n",
      "\t\"BillingState\" NVARCHAR(40), \n",
      "\t\"BillingCountry\" NVARCHAR(40), \n",
      "\t\"BillingPostalCode\" NVARCHAR(10), \n",
      "\t\"Total\" NUMERIC(10, 2) NOT NULL, \n",
      "\tPRIMARY KEY (\"InvoiceId\"), \n",
      "\tFOREIGN KEY(\"CustomerId\") REFERENCES \"Customer\" (\"CustomerId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Invoice table:\n",
      "InvoiceId\tCustomerId\tInvoiceDate\tBillingAddress\tBillingCity\tBillingState\tBillingCountry\tBillingPostalCode\tTotal\n",
      "1\t2\t2009-01-01 00:00:00\tTheodor-Heuss-Straße 34\tStuttgart\tNone\tGermany\t70174\t1.98\n",
      "2\t4\t2009-01-02 00:00:00\tUllevålsveien 14\tOslo\tNone\tNorway\t0171\t3.96\n",
      "3\t8\t2009-01-03 00:00:00\tGrétrystraat 63\tBrussels\tNone\tBelgium\t1000\t5.94\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query_checker` with `SELECT c.Country, SUM(i.Total) AS TotalSales FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId GROUP BY c.Country ORDER BY TotalSales DESC`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m\n",
      "SELECT c.Country, SUM(i.Total) AS TotalSales \n",
      "FROM Customer c \n",
      "JOIN Invoice i ON c.CustomerId = i.CustomerId \n",
      "GROUP BY c.Country \n",
      "ORDER BY TotalSales DESC\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query` with `SELECT c.Country, SUM(i.Total) AS TotalSales FROM Customer c JOIN Invoice i ON c.CustomerId = i.CustomerId GROUP BY c.Country ORDER BY TotalSales DESC`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m[('USA', 523.0600000000003), ('Canada', 303.9599999999999), ('France', 195.09999999999994), ('Brazil', 190.09999999999997), ('Germany', 156.48), ('United Kingdom', 112.85999999999999), ('Czech Republic', 90.24000000000001), ('Portugal', 77.23999999999998), ('India', 75.25999999999999), ('Chile', 46.62), ('Ireland', 45.62), ('Hungary', 45.62), ('Austria', 42.62), ('Finland', 41.620000000000005), ('Netherlands', 40.62), ('Norway', 39.62), ('Sweden', 38.620000000000005), ('Poland', 37.620000000000005), ('Italy', 37.620000000000005), ('Denmark', 37.620000000000005), ('Australia', 37.620000000000005), ('Argentina', 37.620000000000005), ('Spain', 37.62), ('Belgium', 37.62)]\u001b[0m\u001b[32;1m\u001b[1;3mThe total sales per country are as follows:\n",
      "\n",
      "- USA: $523.06\n",
      "- Canada: $303.96\n",
      "- France: $195.10\n",
      "- Brazil: $190.10\n",
      "- Germany: $156.48\n",
      "- United Kingdom: $112.86\n",
      "- Czech Republic: $90.24\n",
      "- Portugal: $77.24\n",
      "- India: $75.26\n",
      "- Chile: $46.62\n",
      "\n",
      "(Only the top 10 results are shown)\n",
      "\n",
      "The country whose customers spent the most is the USA.\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'The total sales per country are as follows:\\n\\n- USA: $523.06\\n- Canada: $303.96\\n- France: $195.10\\n- Brazil: $190.10\\n- Germany: $156.48\\n- United Kingdom: $112.86\\n- Czech Republic: $90.24\\n- Portugal: $77.24\\n- India: $75.26\\n- Chile: $46.62\\n\\n(Only the top 10 results are shown)\\n\\nThe country whose customers spent the most is the USA.'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\n",
    "    \"List the total sales per country. Which country's customers spent the most?\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_list_tables` with ``\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[38;5;200m\u001b[1;3mAlbum, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_schema` with `Playlist, PlaylistTrack`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE \"Playlist\" (\n",
      "\t\"PlaylistId\" INTEGER NOT NULL, \n",
      "\t\"Name\" NVARCHAR(120), \n",
      "\tPRIMARY KEY (\"PlaylistId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Playlist table:\n",
      "PlaylistId\tName\n",
      "1\tMusic\n",
      "2\tMovies\n",
      "3\tTV Shows\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE \"PlaylistTrack\" (\n",
      "\t\"PlaylistId\" INTEGER NOT NULL, \n",
      "\t\"TrackId\" INTEGER NOT NULL, \n",
      "\tPRIMARY KEY (\"PlaylistId\", \"TrackId\"), \n",
      "\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n",
      "\tFOREIGN KEY(\"PlaylistId\") REFERENCES \"Playlist\" (\"PlaylistId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from PlaylistTrack table:\n",
      "PlaylistId\tTrackId\n",
      "1\t3402\n",
      "1\t3389\n",
      "1\t3390\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query_checker` with `SELECT p.Name, COUNT(pt.TrackId) AS TotalTracks FROM Playlist p JOIN PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId GROUP BY p.PlaylistId, p.Name ORDER BY p.Name`\n",
      "responded: Now that I have the schema for the `Playlist` and `PlaylistTrack` tables, I can write a query to show the total number of tracks in each playlist, including the playlist name in the result. I will join the `Playlist` and `PlaylistTrack` tables on the `PlaylistId` and use the `COUNT` aggregate function to get the total number of tracks for each playlist. Let's construct the query.\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m\n",
      "SELECT p.Name, COUNT(pt.TrackId) AS TotalTracks \n",
      "FROM Playlist p \n",
      "JOIN PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId \n",
      "GROUP BY p.PlaylistId, p.Name \n",
      "ORDER BY p.Name\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query` with `SELECT p.Name, COUNT(pt.TrackId) AS TotalTracks FROM Playlist p JOIN PlaylistTrack pt ON p.PlaylistId = pt.PlaylistId GROUP BY p.PlaylistId, p.Name ORDER BY p.Name`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m[('90’s Music', 1477), ('Brazilian Music', 39), ('Classical', 75), ('Classical 101 - Deep Cuts', 25), ('Classical 101 - Next Steps', 25), ('Classical 101 - The Basics', 25), ('Grunge', 15), ('Heavy Metal Classic', 26), ('Music', 3290), ('Music', 3290), ('Music Videos', 1), ('On-The-Go 1', 1), ('TV Shows', 213), ('TV Shows', 213)]\u001b[0m\u001b[32;1m\u001b[1;3mThe total number of tracks in each playlist, along with the playlist name, is as follows:\n",
      "\n",
      "- 90’s Music: 1477 tracks\n",
      "- Brazilian Music: 39 tracks\n",
      "- Classical: 75 tracks\n",
      "- Classical 101 - Deep Cuts: 25 tracks\n",
      "- Classical 101 - Next Steps: 25 tracks\n",
      "- Classical 101 - The Basics: 25 tracks\n",
      "- Grunge: 15 tracks\n",
      "- Heavy Metal Classic: 26 tracks\n",
      "- Music: 3290 tracks (appears twice, possibly due to a data duplication issue)\n",
      "- Music Videos: 1 track\n",
      "- On-The-Go 1: 1 track\n",
      "- TV Shows: 213 tracks (also appears twice, possibly due to a data duplication issue)\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'The total number of tracks in each playlist, along with the playlist name, is as follows:\\n\\n- 90’s Music: 1477 tracks\\n- Brazilian Music: 39 tracks\\n- Classical: 75 tracks\\n- Classical 101 - Deep Cuts: 25 tracks\\n- Classical 101 - Next Steps: 25 tracks\\n- Classical 101 - The Basics: 25 tracks\\n- Grunge: 15 tracks\\n- Heavy Metal Classic: 26 tracks\\n- Music: 3290 tracks (appears twice, possibly due to a data duplication issue)\\n- Music Videos: 1 track\\n- On-The-Go 1: 1 track\\n- TV Shows: 213 tracks (also appears twice, possibly due to a data duplication issue)'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\n",
    "    \"Show the total number of tracks in each playlist. The Playlist name should be included in the result.\"\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "agent可以自动修复不存在的键值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "\n",
      "\u001b[1m> Entering new AgentExecutor chain...\u001b[0m\n",
      "\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_list_tables` with ``\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[38;5;200m\u001b[1;3mAlbum, Artist, Customer, Employee, Genre, Invoice, InvoiceLine, MediaType, Playlist, PlaylistTrack, Track\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_schema` with `Artist, InvoiceLine, Track, Album`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[33;1m\u001b[1;3m\n",
      "CREATE TABLE \"Album\" (\n",
      "\t\"AlbumId\" INTEGER NOT NULL, \n",
      "\t\"Title\" NVARCHAR(160) NOT NULL, \n",
      "\t\"ArtistId\" INTEGER NOT NULL, \n",
      "\tPRIMARY KEY (\"AlbumId\"), \n",
      "\tFOREIGN KEY(\"ArtistId\") REFERENCES \"Artist\" (\"ArtistId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Album table:\n",
      "AlbumId\tTitle\tArtistId\n",
      "1\tFor Those About To Rock We Salute You\t1\n",
      "2\tBalls to the Wall\t2\n",
      "3\tRestless and Wild\t2\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE \"Artist\" (\n",
      "\t\"ArtistId\" INTEGER NOT NULL, \n",
      "\t\"Name\" NVARCHAR(120), \n",
      "\tPRIMARY KEY (\"ArtistId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Artist table:\n",
      "ArtistId\tName\n",
      "1\tAC/DC\n",
      "2\tAccept\n",
      "3\tAerosmith\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE \"InvoiceLine\" (\n",
      "\t\"InvoiceLineId\" INTEGER NOT NULL, \n",
      "\t\"InvoiceId\" INTEGER NOT NULL, \n",
      "\t\"TrackId\" INTEGER NOT NULL, \n",
      "\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n",
      "\t\"Quantity\" INTEGER NOT NULL, \n",
      "\tPRIMARY KEY (\"InvoiceLineId\"), \n",
      "\tFOREIGN KEY(\"TrackId\") REFERENCES \"Track\" (\"TrackId\"), \n",
      "\tFOREIGN KEY(\"InvoiceId\") REFERENCES \"Invoice\" (\"InvoiceId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from InvoiceLine table:\n",
      "InvoiceLineId\tInvoiceId\tTrackId\tUnitPrice\tQuantity\n",
      "1\t1\t2\t0.99\t1\n",
      "2\t1\t4\t0.99\t1\n",
      "3\t2\t6\t0.99\t1\n",
      "*/\n",
      "\n",
      "\n",
      "CREATE TABLE \"Track\" (\n",
      "\t\"TrackId\" INTEGER NOT NULL, \n",
      "\t\"Name\" NVARCHAR(200) NOT NULL, \n",
      "\t\"AlbumId\" INTEGER, \n",
      "\t\"MediaTypeId\" INTEGER NOT NULL, \n",
      "\t\"GenreId\" INTEGER, \n",
      "\t\"Composer\" NVARCHAR(220), \n",
      "\t\"Milliseconds\" INTEGER NOT NULL, \n",
      "\t\"Bytes\" INTEGER, \n",
      "\t\"UnitPrice\" NUMERIC(10, 2) NOT NULL, \n",
      "\tPRIMARY KEY (\"TrackId\"), \n",
      "\tFOREIGN KEY(\"MediaTypeId\") REFERENCES \"MediaType\" (\"MediaTypeId\"), \n",
      "\tFOREIGN KEY(\"GenreId\") REFERENCES \"Genre\" (\"GenreId\"), \n",
      "\tFOREIGN KEY(\"AlbumId\") REFERENCES \"Album\" (\"AlbumId\")\n",
      ")\n",
      "\n",
      "/*\n",
      "3 rows from Track table:\n",
      "TrackId\tName\tAlbumId\tMediaTypeId\tGenreId\tComposer\tMilliseconds\tBytes\tUnitPrice\n",
      "1\tFor Those About To Rock (We Salute You)\t1\t1\t1\tAngus Young, Malcolm Young, Brian Johnson\t343719\t11170334\t0.99\n",
      "2\tBalls to the Wall\t2\t2\t1\tNone\t342562\t5510424\t0.99\n",
      "3\tFast As a Shark\t3\t2\t1\tF. Baltes, S. Kaufman, U. Dirkscneider & W. Hoffman\t230619\t3990994\t0.99\n",
      "*/\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query_checker` with `SELECT ar.Name, SUM(il.Quantity) AS TotalSales FROM InvoiceLine il JOIN Track t ON il.TrackId = t.TrackId JOIN Album al ON t.AlbumId = al.AlbumId JOIN Artist ar ON al.ArtistId = ar.ArtistId GROUP BY ar.ArtistId ORDER BY TotalSales DESC LIMIT 3`\n",
      "responded: To find the top 3 best selling artists, I will need to:\n",
      "\n",
      "1. Join the `InvoiceLine`, `Track`, and `Album` tables to get the total sales for each track.\n",
      "2. Sum the quantity from `InvoiceLine` grouped by the `ArtistId` from the `Album` table.\n",
      "3. Join the result with the `Artist` table to get the artist names.\n",
      "4. Order the results by the total sales in descending order and limit the results to the top 3.\n",
      "\n",
      "Let's construct the SQL query to get this information.\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m\n",
      "SELECT ar.Name, SUM(il.Quantity) AS TotalSales \n",
      "FROM InvoiceLine il \n",
      "JOIN Track t ON il.TrackId = t.TrackId \n",
      "JOIN Album al ON t.AlbumId = al.AlbumId \n",
      "JOIN Artist ar ON al.ArtistId = ar.ArtistId \n",
      "WHERE ar.Name NOT NULL \n",
      "GROUP BY ar.ArtistId \n",
      "ORDER BY TotalSales DESC \n",
      "LIMIT 3\u001b[0m\u001b[32;1m\u001b[1;3m\n",
      "Invoking: `sql_db_query` with `SELECT ar.Name, SUM(il.Quantity) AS TotalSales FROM InvoiceLine il JOIN Track t ON il.TrackId = t.TrackId JOIN Album al ON t.AlbumId = al.AlbumId JOIN Artist ar ON al.ArtistId = ar.ArtistId GROUP BY ar.ArtistId ORDER BY TotalSales DESC LIMIT 3`\n",
      "\n",
      "\n",
      "\u001b[0m\u001b[36;1m\u001b[1;3m[('Iron Maiden', 140), ('U2', 107), ('Metallica', 91)]\u001b[0m\u001b[32;1m\u001b[1;3mThe top 3 best selling artists are:\n",
      "\n",
      "1. Iron Maiden with 140 sales\n",
      "2. U2 with 107 sales\n",
      "3. Metallica with 91 sales\u001b[0m\n",
      "\n",
      "\u001b[1m> Finished chain.\u001b[0m\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'The top 3 best selling artists are:\\n\\n1. Iron Maiden with 140 sales\\n2. U2 with 107 sales\\n3. Metallica with 91 sales'"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "agent_executor.run(\"Who are the top 3 best selling artists?\")"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "langchains",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
  